﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using KVB.Common;
using Customer_Profile.Admin;

namespace Customer_Profile.eCard.Agent
{
    public partial class CardStockImport : System.Web.UI.Page
    {
        string filename = "";
        DataSet ds = new DataSet();
        string strUserID = "";
        string strUserName = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            Comm.CheckSession(this.Page, "/login.aspx", "usermodel");
            if (Session["usermodel"] != null)
            {
                strUserID = ((UserInfoEntity)Session["usermodel"]).UserLoginId;
                strUserName = ((UserInfoEntity)Session["usermodel"]).UserName;
            }
            else
            {
                Log.WriteDefaultLog("main_01 Page_Load", "Session is null");
                return;
            }
            if (!Page.IsPostBack)
            {
                gridBankcflowDetail.Visible = false;
            }
        }

        public string fileupdown()
        {
            if (Path.GetExtension(FileUpload1.FileName) == ".xlsx")
            {
                string filename = FileUpload1.FileName; //FileUpload1.PostedFile.FileName.Substring(FileUpload1.PostedFile.FileName.LastIndexOf("\\"));
                FileUpload1.PostedFile.SaveAs(Server.MapPath("CardStock-moduleTemp\\") + filename);//上传文件
                FileUpload1.PostedFile.InputStream.Close();
                lblWarning.Text = "数据上传成功";
                Response.Write("<script>alert('数据上传成功！');</script>");
                return filename;
            }
            else
            {
                //Response.Write("<script language='Javascript'>alert('请选择模板xlsx文件！');</script>");
                lblWarning.Text = "请选择模板xlsx文件！";
                return "";
            }
        }

        public void fillGrid(string filename, string excelName)
        {
            try
            {
                string conn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Server.MapPath("CardStock-moduleTemp\\") + filename + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件  
                string sqlin = "SELECT * FROM [" + excelName + "$]";
                OleDbCommand olecommand = new OleDbCommand(sqlin, new OleDbConnection(conn));
                OleDbDataAdapter adapterin = new OleDbDataAdapter(olecommand);
                //DataSet dsin = new DataSet();
                adapterin.Fill(ds);
                this.gridBankcflowDetail.DataSource = ds.Tables[0];
                this.gridBankcflowDetail.DataBind();
            }
            catch (Exception ex)
            {
                lblWarning.Text = ex.ToString();
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                filename = fileupdown();
                if (!filename.Equals(""))
                {
                    hidFileName.Value = filename;
                    fillGrid(filename, "Sheet1");
                    gridBankcflowDetail.Visible = true;
                }
            }
            catch (Exception ex)
            {
                lblWarning.Text = ex.ToString();
            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            fillGrid(hidFileName.Value, "Sheet1");

            SqlConnection conn = null;
            DB.Connection(ref conn);
            SqlTransaction trans = conn.BeginTransaction();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = conn;
            sqlCommand.Transaction = trans;

            try
            {
                ds.Tables[0].Rows[0].Delete();
                ds.AcceptChanges();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    //添加银行账单信息
                    sqlCommand.Parameters.Clear();
                    string card_no = dr[0].ToString();

                    sqlCommand.Parameters.Add("@card_no", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters["@card_no"].Value = card_no;

                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "eCard_cardStock_batchImport";
                    sqlCommand.ExecuteNonQuery();
                }
                trans.Commit();
                Response.Write("<script>alert('导入数据成功！');</script>");
                lblWarning.Text = "导入数据成功";
                hidFileName.Value = "";
            }
            catch (Exception ex)
            {
                lblWarning.Text = "导入数据出错,请联系管理员!" + ex.ToString();
                KVB.Common.Log.WriteDefaultLog("CardStockImport_CpartyDao.AddBankTransfer", ex.ToString());
                trans.Rollback();
                Response.Write("<script>alert('导入数据出错！'+" + ex.ToString() + ");</script>");
            }
            finally
            {
                DB.ConnDispose(ref conn);
                trans.Dispose();
                conn.Dispose();
            }
        }
    }
}